--    Author    : ChenErHao
--    Name      : EDW.LOAB.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-10  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE EDW.LOAB PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 
SELECT 
   FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE            -- 跑批日期
  ,DATA_ORG_CODE                                                                  -- 金融机构编码
  ,CASE WHEN CUSTOMER_TYPE='0' THEN '2' ELSE CUSTOMER_TYPE END AS CUSTOMER_TYPE         -- 客户类型
  ,CUSTOMER_ID                                                                    -- 借款人代码
  ,NVL(LPIC,'9999')                                                               -- 贷款主体行业类别
  ,REGIST_ADD_CODE                                                                -- 借款人注册地编码
  ,NVL(ENTERPRISE_INVESTOR,'9999')                                                -- 企业出资人经济成分
  ,NVL(ENTERPRISE_SCALE,'9999') ENTERPRISE_SCALE                                  -- 企业规模
  ,LOAN_IOUS_CODE                                                                 -- 贷款借据编码
  ,PRODUCT_CATEGORY                                                               -- 产品类别
  ,LOANS_ACTUALLY                                                                 -- 贷款实际投向
  ,LENDING_DATE                                                                   -- 贷款发放日期
  ,LOAN_DUE_DATE                                                                  -- 贷款到期日期
  ,ROLL_OVER_DATE                                                                 -- 展期到期日期
  ,LOAN_CUR_TYPE                                                                  -- 贷款币种
  ,LOAN_BALANCE                                                                   -- 贷款余额
  ,RATE_FIXED_FLOAT                                                               -- 利率是否固定
  ,RATE_LEVEL                                                                     -- 利率水平
  ,GUARANTY_STYLE                                                                 -- 贷款担保方式
  ,LOAN_QUALITY                                                                   -- 贷款质量
  ,NVL(T1.LOAN_STATUS,'') LOAN_STATUS                                              -- 贷款状态
  ,CASE WHEN LENDING_DATE='' OR LENDING_DATE IS NULL OR LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL THEN 9999
       WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=6  THEN 10
       WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=12 THEN 20
       WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=36 THEN 30
       WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=60 THEN 40
       WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)<=120 THEN 50
       WHEN MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE)>120 THEN 60
     ELSE 9999
   END AS LOANS_PERIOD                                                            -- 贷款期限区间_专题
  ,NVL(T3.AREA_NO_ID_1,'9999') AS OWNS_PROV_ID                                     -- 省
  ,NVL(T3.AREA_NO_ID,'9999') AS OWNS_AREA_ID                                     -- 市
  ,NVL(T3.AREA_NO_ID,'9999') AS OWNS_CITY_ID                                     -- 县
  ,T2.JRTJ_AREA_CODE                                                             -- 大集中地区编码
  ,CASE WHEN T2.ORG_LVL_ID_2='A05' THEN 'A05'
     ELSE T2.ORG_LVL_ID_3
    END AS DATA_ORG_TYPE                                                           -- 数据机构类型
  ,LOAN_BALANCE*RATE_LEVEL AS  LOAN_BALANCE_RATE                                  -- 余额加权值
  ,CASE WHEN LOAN_BALANCE/10000<=5 THEN 'A1'
        WHEN LOAN_BALANCE/10000<=10 THEN 'A2'
        WHEN LOAN_BALANCE/10000<=30 THEN 'A3'
        WHEN LOAN_BALANCE/10000<=50 THEN 'A4'
        WHEN LOAN_BALANCE/10000<=100 THEN 'B1'
        WHEN LOAN_BALANCE/10000<=300 THEN 'B2'
        WHEN LOAN_BALANCE/10000<=500 THEN 'B3'
        WHEN LOAN_BALANCE/10000<=800 THEN 'C1'
        WHEN LOAN_BALANCE/10000<=1000 THEN 'C2'
        WHEN LOAN_BALANCE/10000<=5000 THEN 'C3'
        WHEN LOAN_BALANCE/10000<=10000 THEN 'D'
        WHEN LOAN_BALANCE/10000<=100000 THEN 'E'
        WHEN LOAN_BALANCE/10000>100000 THEN 'F'
        ELSE '9999'
    END AMT_INTERVAL_ID                                                          -- 金额区间
  ,CASE WHEN LOAN_STATUS ='FS03' OR LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL
           THEN 0
        WHEN LOAN_STATUS ='FS01'
           THEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,'#V_LINE_DATE#'))
        WHEN LOAN_STATUS ='FS02'
           THEN CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))
        ELSE 0 
    END SHENGYU_PERIOD_M                                                            -- 剩余期限
  ,CASE WHEN ROLL_OVER_DATE !='' THEN 1 ELSE 0 END OVER_ZHANQI_ID                    -- 是否展期
  ,CASE WHEN RATE_LEVEL<=3 THEN 10
        WHEN  RATE_LEVEL<=5 THEN 20
        WHEN  RATE_LEVEL<=7 THEN 30
        WHEN  RATE_LEVEL<=9 THEN 40
        WHEN  RATE_LEVEL<=11 THEN 50
        WHEN  RATE_LEVEL<=13 THEN 60
        WHEN  RATE_LEVEL > 13  THEN 70 ELSE '' 
     END RATE_LEVEL_QJ                                                              -- 利率区间
  ,CASE WHEN LOAN_STATUS ='FS03' OR LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL
           THEN 9999
       WHEN LOAN_STATUS ='FS01' AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))<=6  THEN 'A'
       WHEN LOAN_STATUS ='FS02' AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))<=6  THEN 'A'
       WHEN LOAN_STATUS ='FS01' AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))>6  AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))<=12 THEN 'B'
       WHEN LOAN_STATUS ='FS02' AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))>6  AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))<=12 THEN 'B'     
       WHEN LOAN_STATUS ='FS01' AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))>12 AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))<=36 THEN 'C'
       WHEN LOAN_STATUS ='FS02' AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))>12 AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))<=36 THEN 'C'
       WHEN LOAN_STATUS ='FS01' AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))>36 AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))<=60 THEN 'D'
       WHEN LOAN_STATUS ='FS02' AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))>36 AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))<=60 THEN 'D'
       WHEN LOAN_STATUS ='FS01' AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))>60 AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))<=120 THEN 'E'
       WHEN LOAN_STATUS ='FS02' AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))>60 AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))<=120 THEN 'E'
       WHEN LOAN_STATUS ='FS01' AND CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE ,'#V_LINE_DATE#'))>120 THEN 'F'
       WHEN LOAN_STATUS ='FS02' AND CEIL(MONTHS_BETWEEN(ROLL_OVER_DATE,'#V_LINE_DATE#'))>120 THEN 'F'
       ELSE 9999 
     END SHENGYU_PERIOD                                                                                                       -- 剩余期限区间
  ,CASE WHEN LOAN_STATUS<>'FS03' OR CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))<=0 THEN 9999
     WHEN CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))<=30 THEN 'A'
     WHEN CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))<=90 THEN 'B'
     WHEN CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))<=180 THEN 'C'
     WHEN CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))<=270 THEN 'D'
     WHEN CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))<=360 THEN 'E'
     WHEN CEIL(DATEDIFF('#V_LINE_DATE#',NVL(ROLL_OVER_DATE,LOAN_DUE_DATE)))>360  THEN 'F' 
     ELSE '9999' 
    END OVER_YUQI_ID                                                                                                              --逾期期限
  ,CASE WHEN LENDING_DATE='' OR LENDING_DATE IS NULL OR LOAN_DUE_DATE='' OR LOAN_DUE_DATE IS NULL THEN 9999
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=6  THEN 'A'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=12 THEN 'B'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=18 THEN 'C1'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=24 THEN 'C2'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=30 THEN 'C3'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=36 THEN 'C4'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=42 THEN 'D1'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=48 THEN 'D2'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=54 THEN 'D3'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=60 THEN 'D4'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=66 THEN 'E1'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=72 THEN 'E2'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=78 THEN 'E3'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=84 THEN 'E4'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=90 THEN 'E5'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=96 THEN 'E6'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=102 THEN 'E7'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=108 THEN 'E8'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=114 THEN 'E9'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=120 THEN 'E91'
     WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))>120 THEN 'F1'
     ELSE 9999 
    END LOANS_PERIOD_CUB                                                                                                          --贷款期限区间_多维
  ,CASE WHEN LENDING_DATE IS NULL OR LENDING_DATE='' OR LOAN_DUE_DATE IS NULL OR LOAN_DUE_DATE='' 
              OR CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=0 
            THEN 0
        WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=6  THEN  D.SHORT_SIXM
        WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=12  THEN D.SHORT_ONEY
        WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=36  THEN D.LONG_ONEY
        WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))<=60  THEN D.LONG_THREEY
        WHEN CEIL(MONTHS_BETWEEN(LOAN_DUE_DATE,LENDING_DATE))>60  THEN D.LONG_FIVEY
   END AS DPS_RATE                                                                                                                  --基准利率
  ,1 AS STROKE_COUNT                                                              -- 笔数
  ,DATA_SRC_ORG
FROM ODS.LOAB T1
LEFT JOIN DMCODE.T_ORG_BIZ_LVL T2 ON T1.DATA_DATE='#V_DATA_DATE#' AND T1.DATA_ORG_CODE=t2.ORG_ID
LEFT JOIN DMCODE.T_AREA_CODE T3 ON T1.DATA_DATE = '#V_DATA_DATE#' AND T2.AREA_NO_ID = T3.AREA_NO_ID
JOIN DMCODE.T_LOAN_RATE D
WHERE T1.DATA_DATE = '#V_DATA_DATE#'
 AND NVL(T1.LENDING_DATE,'10000101') BETWEEN D.BEGIN_DATE AND D.END_DATE
;
